﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Xml;
using System.Xml.Linq;

using Stud.DTOs;
using System.Configuration;

namespace Stud.DataAccess
{
    public class Queries
    {
        string conStr;

        public Queries()
        {
            conStr = ConfigurationManager.ConnectionStrings["SchoolDBConString"].ToString();
        }
        //public StudentInfo GetStudentFromXML(long rollNo, string dept)
        //{
        //    XDocument xDoc = XDocument.Load(@"D:\Student\UTPL\student.xml");
        //    XElement students = xDoc.Element("students");
        //    var stud=students.Elements("student")
        //        .Where(x=>x.Attribute("deptartment").Value.Equals(dept)
        //}
        public void DeleteStudent(StudentInfo stud)
        {
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                var stTab = dataContext.Students.Where(st => st.RollNo == stud.RollNo).FirstOrDefault();

                dataContext.Students.DeleteOnSubmit(stTab);

                dataContext.SubmitChanges();
            }
        }
        public void DeleteStudentByRollNo(long rollNo)
        {
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                //var stFee= dataContext.Fees.w
                var stTab = dataContext.Students.Where(st => st.RollNo == rollNo).FirstOrDefault();

                dataContext.Students.DeleteOnSubmit(stTab);

                dataContext.SubmitChanges();
            }
        }

        public void UpdateStudent(StudentInfo stud)
        {

            #region LINQ demo code
            //List<StudentInfo> students = new List<StudentInfo>();
            //students.Add(new StudentInfo { Age = 21, Name = "arun1", RollNo = 10 });
            //students.Add(new StudentInfo { Age = 22, Name = "arun2", RollNo = 11 });
            //students.Add(new StudentInfo { Age = 20, Name = "arun3", RollNo = 12 });
            //students.Add(new StudentInfo { Age = 23, Name = "arun4", RollNo = 13 });


            //List<StudentInfo> stud2 = (from st in students
            //                           select new StudentInfo { Name = st.Name, RollNo = st.RollNo, Age = st.Age }).ToList();
            //StudentInfo stud4 = (from st in students
            //                     where st.RollNo == 11
            //                     select new StudentInfo { Name = st.Name, RollNo = st.RollNo, Age = st.Age }).FirstOrDefault();

            //StudentInfo strud3 = students.Where(st => st.RollNo == 11).FirstOrDefault();

            //List<int> nos = new List<int>();
            //nos.Add(10);
            //nos.Add(30);
            //nos.Add(20);
            //nos.Add(40);
            //nos.Add(15);

            //var k=nos.Where(n => n == 20);
            //if (k.Count() == 0)
            //{
            //    //not found
            //}
            //else
            //    //found





            //foreach (var student in students)
            //{
            //    if (student.RollNo == 11)
            //    {
            //        //print student.Name;
            //        break;
            //    }
            //}






            #endregion

            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                var stTab = dataContext.Students.Where(st => st.RollNo == stud.RollNo).FirstOrDefault();

                stTab.Name = stud.Name;
                stTab.Age = stud.Age;

                dataContext.SubmitChanges();
            }
        }

        public void AddStudents(List<StudentInfo> studs)
        {
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                Student studentTab = null;
                foreach (var stud in studs)
                {
                    studentTab = new Student();
                    studentTab.RollNo = stud.RollNo;
                    studentTab.Name = stud.Name;
                    studentTab.Age = stud.Age;
                    dataContext.Students.InsertOnSubmit(studentTab);
                }

                dataContext.SubmitChanges();
            }
        }


        public long AddStudent2(StudentInfo stud)
        {
            long id = 0;
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                using (TransactionScope scope = new TransactionScope())
                {

                    try
                    {
                        Student2 studentTab2 = new Student2();
                        studentTab2.Roll = stud.RollNo;
                        studentTab2.Name = stud.Name;
                        studentTab2.Age = stud.Age;

                        dataContext.Student2s.InsertOnSubmit(studentTab2);
                        dataContext.SubmitChanges();
                        id = studentTab2.StudId;
                        int k = Int32.Parse("abcd");
                        scope.Complete();
                    }
                    catch (Exception ex)
                    {
                        scope.Dispose();
                        throw ex;
                    }
                }


                return id;
            }
        }


        public void AddStudent(StudentInfo stud)
        {
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                Student studentTab = new Student();
                studentTab.RollNo = stud.RollNo;
                studentTab.Name = stud.Name;
                studentTab.Age = stud.Age;

                dataContext.Students.InsertOnSubmit(studentTab);
                dataContext.SubmitChanges();

            }
        }
        public List<StudentInfo> GetStudents()
        {
            conStr = ConfigurationManager.ConnectionStrings["SchoolDBConString"].ToString();
            using (SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr))
            {
                var studs = (from st in dataContext.Students
                             select new StudentInfo
                             {
                                 RollNo = st.RollNo,
                                 Name = st.Name,
                                 Age = st.Age
                             }).ToList();
                return studs;



                //var studs2 = from st in dataContext.Students
                //             select st;
                //List<StudentInfo> listStud = new List<StudentInfo>();
                //StudentInfo sinfo;
                //foreach (var stud in studs2)
                //{
                //    sinfo = new StudentInfo
                //    {
                //        RollNo = stud.RollNo,
                //        Name = stud.Name,
                //        Age = stud.Age
                //    };
                //    listStud.Add(sinfo);
                //}
                //return listStud;
            }
        }
        public StudentInfo GetStudentByRollNo(long rollNo)
        {
            conStr = ConfigurationManager.ConnectionStrings["SchoolDBConString"].ToString();
            SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr);
            var stud = (from st in dataContext.Students
                        where st.RollNo == rollNo
                        select new StudentInfo
                        {
                            RollNo = st.RollNo,
                            Name = st.Name,
                            Age = st.Age
                        }).FirstOrDefault();
            return stud;
        }

        public List<StudentAllInfo> GetStudentInfo()
        {
            conStr = ConfigurationManager.ConnectionStrings["SchoolDBConString"].ToString();
            SchoolDBDataContext dataContext = new SchoolDBDataContext(conStr);
            var stud = (from st in dataContext.Students
                        from fe in dataContext.Fees
                        //where st.RollNo == fe.RollNo
                        select new StudentAllInfo
                        {
                            RollNo = st.RollNo,
                            Name = st.Name,
                            Age = st.Age,
                            TrnId = fe.TrnId,
                            TrnDate = fe.TrnDate,
                            TrnAmount = fe.TrnAmount
                        }).ToList();
            return stud;
        }
    }
}
